Represents all measures related to GL ANALYSIS.
| Attribute | Description (where applicable) |
Aggregation of measures is based on SUM if not explicitly noted.
This section describes the basic measures in the cube. These are either measures defined in the core Information Source, in the model itself or in the ETL process. The columns have the following meaning:
| Measure | Calculated Y/N | Contains Time Intelligence Y/N | Definition/Note (where applicable) | Aggregation Type | Hidden Y/N | Tabular Expression |
| ACT | Y | N | SUM | N | SUM('GL ANALYSIS'[ACT_PERIOD_DOM]) | |
| ACT (Acc Calendar) | N | IF (HASONEVALUE('ACCOUNTING PERIOD'[Accounting Year]), SUMX('GL ANALYSIS', [ACT_OB_PERIOD_DOM] + [ACT_PERIOD_DOM])) // Uses the HASONEVALUE function to suppress display of totals that sum up multiple years. | ||||
| ACT Credit | Y | N | SUM | N | SUM('GL ANALYSIS'[ACT_PERIOD_CREDIT_DOM]) | |
| ACT Credit Trans | Y | N | SUM | N | SUM('GL ANALYSIS'[ACT_PERIOD_CREDIT_CURR]) | |
| ACT Credit XR1 (Period Based) | Y | N | SUM | N | IF(HASONEVALUE('CURRENCY CODE X-RATE 1'[Currency Code X-Rate 1]), VAR RestatedAmount = ADDCOLUMNS( SUMMARIZE('GL ANALYSIS' ,'REPORTING PERIOD'[ID],'REPORTING FROM CURRENCY'[Currency Code]) ,"@GLAmount",CALCULATE([ACT Credit]) ,"@XRates" ,CALCULATE(SELECTEDVALUE('X-RATES 1'[CURRENCYRATE])) ) VAR XAmount = SUMX(RestatedAmount, [@GLAmount] * [@XRates]) RETURN XAmount ) | |
| ACT Credit XR2 (Period Based) | Y | N | SUM | N | IF(HASONEVALUE('CURRENCY CODE X-RATE 2'[Currency Code X-Rate 2]), VAR RestatedAmount = ADDCOLUMNS( SUMMARIZE('GL ANALYSIS' ,'REPORTING PERIOD'[ID],'REPORTING FROM CURRENCY 2'[Currency Code]) ,"@GLAmount",CALCULATE([ACT Credit]) ,"@XRates" ,CALCULATE(SELECTEDVALUE('X-RATES 2'[CURRENCYRATE])) ) VAR XAmount = SUMX(RestatedAmount, [@GLAmount] * [@XRates]) RETURN XAmount ) | |
| ACT Credit YTD Trans | Y | Y | SUM | N | CALCULATE([ACT Credit Trans], DATESYTD('REPORTING PERIOD'[ID])) + CALCULATE(SUM('GL ANALYSIS'[OB_PERIOD_CURR]), DATESYTD('REPORTING PERIOD'[ID])) | |
| ACT Debit | Y | N | SUM | N | SUM('GL ANALYSIS'[ACT_PERIOD_DEBET_DOM]) | |
| ACT Debit Trans | Y | N | SUM | N | SUM('GL ANALYSIS'[ACT_PERIOD_DEBET_CURR]) | |
| ACT Debit XR1 (Period Based) | Y | N | SUM | N | IF(HASONEVALUE('CURRENCY CODE X-RATE 1'[Currency Code X-Rate 1]), VAR RestatedAmount = ADDCOLUMNS( SUMMARIZE('GL ANALYSIS' ,'REPORTING PERIOD'[ID],'REPORTING FROM CURRENCY'[Currency Code]) ,"@GLAmount",CALCULATE([ACT Debit]) ,"@XRates" ,CALCULATE(SELECTEDVALUE('X-RATES 1'[CURRENCYRATE])) ) VAR XAmount = SUMX(RestatedAmount, [@GLAmount] * [@XRates]) RETURN XAmount ) | |
| ACT Debit XR2 (Period Based) | Y | N | SUM | N | IF(HASONEVALUE('CURRENCY CODE X-RATE 2'[Currency Code X-Rate 2]), VAR RestatedAmount = ADDCOLUMNS( SUMMARIZE('GL ANALYSIS' ,'REPORTING PERIOD'[ID],'REPORTING FROM CURRENCY 2'[Currency Code]) ,"@GLAmount",CALCULATE([ACT Debit]) ,"@XRates" ,CALCULATE(SELECTEDVALUE('X-RATES 2'[CURRENCYRATE])) ) VAR XAmount = SUMX(RestatedAmount, [@GLAmount] * [@XRates]) RETURN XAmount ) | |
| ACT Debit YTD Trans | Y | Y | SUM | N | CALCULATE([ACT Debit Trans], DATESYTD('REPORTING PERIOD'[ID])) + CALCULATE(SUM('GL ANALYSIS'[OB_PERIOD_CURR]), DATESYTD('REPORTING PERIOD'[ID])) | |
| ACT OB | Y | N | SUM | N | IF(ISBLANK([ACT YTD]) , BLANK() ,([ACT YTD] - [ACT])) | |
| ACT OB (Acc Calendar) | N | VAR CurrAccYear = SELECTEDVALUE('ACCOUNTING PERIOD'[Accounting Year]) // Uses the SELECTEDVALUE function to suppress display of totals that sum up multiple years. Var CurrAccPeriod = IF( SELECTEDVALUE('ACCOUNTING PERIOD'[Accounting Period No]) = 0, 1, SELECTEDVALUE('ACCOUNTING PERIOD'[Accounting Period No])) VAR RunningTotal= CALCULATE( [ACT (Acc Calendar)], FILTER(ALL('ACCOUNTING PERIOD'), 'ACCOUNTING PERIOD'[Accounting Year]=CurrAccYear && 'ACCOUNTING PERIOD'[Accounting Period No]< CurrAccPeriod)) RETURN RunningTotal | ||||
| ACT OB Trans | Y | N | SUM | N | IF(ISBLANK([ACT YTD Trans]) , BLANK() ,([ACT YTD Trans] - [ACT Trans])) | |
| ACT OB XR1 (Period Based) | Y | N | SUM | N | SUMX ( VALUES ( COMPANY[ID] ), FIRSTNONBLANKVALUE('REPORTING PERIOD'[ID],[ACT OB]*[Conversion rate XR1])) // ACT OB X-rate (Period Based): [ACT OB] multiplied with the [rate] at each specific period. // FIRSTNONBLANKVALUE is used for handling TOTALS at Quarter and Year level to be the value from the first period within the quarter or year. // VALUES-statement to handle the multi-company scenario where companies have different conversion rates. // If ever to update this code: Heads up on verifing the results on Quarter and Year level in the multi-company scenario. | |
| ACT OB XR1 (YTD Based) | Y | Y | SUM | N | VAR CurrPeriod = MAX('REPORTING PERIOD'[Reporting Period]) VAR MonthsInPeriod = MAX('REPORTING PERIOD'[Months In Period]) RETURN IF('GL ANALYSIS MEASURES'[HasOneValuePeriod] = FALSE(), BLANK() , IF ( CurrPeriod = 1, CALCULATE([ACT OB XR1 (Period Based)]), CALCULATE([ACT YTD XR1 (YTD Based)], DATEADD('REPORTING PERIOD'[ID], -MonthsInPeriod, MONTH) ) )) // ACT OB X-rate (YTD Based): The YTD value from previous period, part from period 1 in which calculation is OB*rate. // NOTE: This measure can only to be presented on Reporting Period level. Can not be used on Quarter, Half year or Year level. | |
| ACT OB XR2 (Period Based) | Y | N | SUM | N | IF('GL ANALYSIS MEASURES'[HasOneValuePeriod] = FALSE(), BLANK(), SUMX ( VALUES ( COMPANY[ID] ), [ACT OB]*[Conversion rate XR2])) // ACT OB X-rate (Period Based): [ACT OB] multiplied with the [rate] at each specific period. // VALUES-statement to handle the multi-company scenario where companies have different conversion rates. | |
| ACT OB XR2 (YTD Based) | Y | Y | SUM | N | VAR CurrPeriod = MAX('REPORTING PERIOD'[Reporting Period]) VAR MonthsInPeriod = MAX('REPORTING PERIOD'[Months In Period]) RETURN IF('GL ANALYSIS MEASURES'[HasOneValuePeriod] = FALSE(), BLANK() , IF ( CurrPeriod = 1, CALCULATE([ACT OB XR2 (Period Based)]), CALCULATE([ACT YTD XR2 (YTD Based)], DATEADD('REPORTING PERIOD'[ID], -MonthsInPeriod, MONTH) ) )) // ACT OB X-rate (YTD Based): The YTD value from previous period, part from period 1 in which calculation is OB*rate. // NOTE: This measure can only to be presented on Reporting Period level. Can not be used on Quarter, Half year or Year level. | |
| ACT PY R12 Trans | Y | Y | SUM | N | CALCULATE([ACT R12 Trans], DATEADD('REPORTING PERIOD'[ID],-12,month)) // ACT PY R12 Trans: This measure displays the last available value if presented for multiple periods, eg by quarter then the value in quarter 1 will be the value from March. | |
| ACT PY R12 XR1 (Period Based) | Y | Y | SUM | N | CALCULATE([ACT R12 XR1 (Period Based)], DATEADD('REPORTING PERIOD'[ID],-12,month)) // ACT PY R12 X-rate (Period Based): R12 X-rate from the same period previous year. // NOTE: This measure can only to be presented on Reporting Period level. Can not be used on Quarter, Half year or Year level. | |
| ACT PY R12 XR1 (YTD Based) | Y | Y | SUM | N | CALCULATE([ACT R12 XR1 (YTD Based)], DATEADD('REPORTING PERIOD'[ID],-12,month)) // ACT PY R12 X-rate (YTD Based): R12 from the same period previous year. | |
| ACT PY R12 XR2 (Period Based) | Y | Y | SUM | N | CALCULATE([ACT R12 XR2 (Period Based)], DATEADD('REPORTING PERIOD'[ID],-12,month)) // ACT PY R12 X-rate (Period Based): R12 X-rate from the same period previous year. // NOTE: This measure can only to be presented on Reporting Period level. Can not be used on Quarter, Half year or Year level. | |
| ACT PY R12 XR2 (YTD Based) | Y | Y | SUM | N | CALCULATE([ACT R12 XR2 (YTD Based)], DATEADD('REPORTING PERIOD'[ID],-12,month)) // ACT PY R12 X-rate (YTD Based): R12 from the same period previous year. | |
| ACT PY Trans | Y | Y | SUM | N | CALCULATE([ACT Trans], DATEADD('REPORTING PERIOD'[ID],-12,month)) | |
| ACT PY XR1 (Period Based) | Y | Y | SUM | N | CALCULATE([ACT XR1 (Period Based)], DATEADD('REPORTING PERIOD'[ID],-12,month)) // ACT PY X-rate (Period Based): ACT X-rate from the same reporting period previous year. | |
| ACT PY XR1 (YTD Based) | Y | Y | SUM | N | CALCULATE([ACT XR1 (YTD Based)], DATEADD('REPORTING PERIOD'[ID],-12,month)) // ACT PY X-rate (YTD Based): The value from the same period last year. | |
| ACT PY XR2 (Period Based) | Y | Y | SUM | N | CALCULATE([ACT XR2 (Period Based)], DATEADD('REPORTING PERIOD'[ID],-12,month)) // ACT PY X-rate (Period Based): ACT X-rate from the same reporting period previous year. | |
| ACT PY XR2 (YTD Based) | Y | Y | SUM | N | CALCULATE([ACT XR2 (YTD Based)], DATEADD('REPORTING PERIOD'[ID],-12,month)) // ACT PY X-rate (YTD Based): The value from the same period last year. | |
| ACT PY YTD Trans | Y | Y | SUM | N | CALCULATE([ACT YTD Trans], DATEADD('REPORTING PERIOD'[ID],-12,month)) | |
| ACT PY YTD XR1 (Period Based) | N | CALCULATE ( [ACT YTD XR1 (Period Based)], DATEADD ( 'REPORTING PERIOD'[ID], -12, MONTH ) ) // ACT PY YTD X-rate (Period Based): ACT YTD Xrate from the same reporting period previous year. | ||||
| ACT PY YTD XR1 (YTD Based) | Y | Y | SUM | N | CALCULATE([ACT YTD XR1 (YTD Based)], DATEADD('REPORTING PERIOD'[ID],-12,month)) // ACT PY YTD X-rate (YTD Based): The YTD value for the same period last year. | |
| ACT PY YTD XR2 (Period Based) | N | CALCULATE ( [ACT YTD XR2 (Period Based)], DATEADD ( 'REPORTING PERIOD'[ID], -12, MONTH ) ) // ACT PY YTD X-rate (Period Based): ACT YTD Xrate from the same reporting period previous year. | ||||
| ACT PY YTD XR2 (YTD Based) | Y | Y | SUM | N | CALCULATE([ACT YTD XR2 (YTD Based)], DATEADD('REPORTING PERIOD'[ID],-12,month)) // ACT PY YTD X-rate (YTD Based): The YTD value for the same period last year. | |
| ACT R12 Trans | Y | Y | SUM | N | VAR First_Period = CALCULATE(MAX('REPORTING PERIOD'[Reporting Year Period]), DATEADD('REPORTING PERIOD'[ID], -11, MONTH)) VAR Last_Period = MAX ( 'REPORTING PERIOD'[Reporting Year Period] ) RETURN CALCULATE( [ACT Trans], FILTER ( ALL ( 'REPORTING PERIOD' ), 'REPORTING PERIOD'[Reporting Year Period] >= First_Period && 'REPORTING PERIOD'[Reporting Year Period] <= Last_Period ) ) // ACT R12 Trans: This measure displays the last available value if presented for multiple periods, eg by quarter then the value in quater 1 will be the value from March. | |
| ACT R12 XR1 (Period Based) | Y | Y | SUM | N | IF('GL ANALYSIS MEASURES'[HasOneValuePeriod] = FALSE(), BLANK(), [ACT XR1 (Period Based)] + CALCULATE([ACT XR1 (Period Based)], DATEADD('REPORTING PERIOD'[ID], -1, MONTH)) + CALCULATE([ACT XR1 (Period Based)], DATEADD('REPORTING PERIOD'[ID], -2, MONTH)) + CALCULATE([ACT XR1 (Period Based)], DATEADD('REPORTING PERIOD'[ID], -3, MONTH)) + CALCULATE([ACT XR1 (Period Based)], DATEADD('REPORTING PERIOD'[ID], -4, MONTH)) + CALCULATE([ACT XR1 (Period Based)], DATEADD('REPORTING PERIOD'[ID], -5, MONTH)) + CALCULATE([ACT XR1 (Period Based)], DATEADD('REPORTING PERIOD'[ID], -6, MONTH)) + CALCULATE([ACT XR1 (Period Based)], DATEADD('REPORTING PERIOD'[ID], -7, MONTH)) + CALCULATE([ACT XR1 (Period Based)], DATEADD('REPORTING PERIOD'[ID], -8, MONTH)) + CALCULATE([ACT XR1 (Period Based)], DATEADD('REPORTING PERIOD'[ID], -9, MONTH)) + CALCULATE([ACT XR1 (Period Based)], DATEADD('REPORTING PERIOD'[ID], -10, MONTH)) + CALCULATE([ACT XR1 (Period Based)], DATEADD('REPORTING PERIOD'[ID], -11, MONTH)) ) // ACT R12 X-rate (Period Based): The sum of [ACT XR1 (Period Based)] for the current month and [ACT XR1 (Period Based)] values from each of the previous 11 months. // NOTE: This measure can only to be presented on Reporting Period level. Can not be used on Quarter, Half year or Year level. | |
| ACT R12 XR1 (YTD Based) | Y | Y | SUM | N | SUMX ( VALUES ( COMPANY[ID] ), [ACT R12]*[Conversion rate XR1]) // ACT R12 X-rate (YTD Based): The R12-value multiplied with the rate at the that specific Reporting period. // VALUES-statement to handle the multi-company scenario where companies have different conversion rates. | |
| ACT R12 XR2 (Period Based) | Y | Y | SUM | N | IF('GL ANALYSIS MEASURES'[HasOneValuePeriod] = FALSE(), BLANK(), [ACT XR2 (Period Based)] + CALCULATE([ACT XR2 (Period Based)], DATEADD('REPORTING PERIOD'[ID], -1, MONTH)) + CALCULATE([ACT XR2 (Period Based)], DATEADD('REPORTING PERIOD'[ID], -2, MONTH)) + CALCULATE([ACT XR2 (Period Based)], DATEADD('REPORTING PERIOD'[ID], -3, MONTH)) + CALCULATE([ACT XR2 (Period Based)], DATEADD('REPORTING PERIOD'[ID], -4, MONTH)) + CALCULATE([ACT XR2 (Period Based)], DATEADD('REPORTING PERIOD'[ID], -5, MONTH)) + CALCULATE([ACT XR2 (Period Based)], DATEADD('REPORTING PERIOD'[ID], -6, MONTH)) + CALCULATE([ACT XR2 (Period Based)], DATEADD('REPORTING PERIOD'[ID], -7, MONTH)) + CALCULATE([ACT XR2 (Period Based)], DATEADD('REPORTING PERIOD'[ID], -8, MONTH)) + CALCULATE([ACT XR2 (Period Based)], DATEADD('REPORTING PERIOD'[ID], -9, MONTH)) + CALCULATE([ACT XR2 (Period Based)], DATEADD('REPORTING PERIOD'[ID], -10, MONTH)) + CALCULATE([ACT XR2 (Period Based)], DATEADD('REPORTING PERIOD'[ID], -11, MONTH)) ) // ACT R12 X-rate (Period Based): The sum of [ACT XR2 (Period Based)] for the current month and [ACT XR2 (Period Based)] values from each of the previous 11 months. // NOTE: This measure can only to be presented on Reporting Period level. Can not be used on Quarter, Half year or Year level. | |
| ACT R12 XR2 (YTD Based) | Y | Y | SUM | N | SUMX ( VALUES ( COMPANY[ID] ), [ACT R12]*[Conversion rate XR2]) // ACT R12 X-rate (YTD Based): The R12-value multiplied with the rate at the that specific Reporting period. // VALUES-statement to handle the multi-company scenario where companies have different conversion rates. | |
| ACT Trans | Y | N | SUM | N | SUM('GL ANALYSIS'[ACT_PERIOD_CURR]) | |
| ACT XR1 (Period Based) | Y | N | SUM | N | IF(HASONEVALUE('CURRENCY CODE X-RATE 1'[Currency Code X-Rate 1]), VAR RestatedAmount = ADDCOLUMNS( SUMMARIZE('GL ANALYSIS' ,'REPORTING PERIOD'[ID],'REPORTING FROM CURRENCY'[Currency Code]) ,"@GLAmount",CALCULATE([ACT]) ,"@XRates" ,CALCULATE(SELECTEDVALUE('X-RATES 1'[CURRENCYRATE])) ) VAR XAmount = SUMX(RestatedAmount, [@GLAmount] * [@XRates]) RETURN XAmount ) | |
| ACT XR1 (YTD Based) | Y | Y | SUM | N | VAR CurrPeriod = MAX('REPORTING PERIOD'[Reporting Period]) RETURN IF('GL ANALYSIS MEASURES'[HasOneValuePeriod] = FALSE(), BLANK(), IF( CurrPeriod = 1, SUMX (VALUES ( COMPANY[ID] ), ([ACT]*[Conversion rate XR1]) ), SUMX (VALUES ( COMPANY[ID] ), ([ACT YTD]*[Conversion rate XR1]) - ([ACT OB]*[Conversion rate PP XR1])) ) ) // ACT X-rate (YTD Based): (ACT YTD X-rate) minus (OB * rate in previous period). Note that OB is the same as the YTD-value in the previous period. // In case no addition of ACT from previous period, this is just the effect of the same YTD value being multiplied with rate in previous period compared with the rate in this period. // NOTE: This measure can only to be presented on Reporting Period level. Can not be used on Quarter, Half year or Year level. HasOnValuePeriod is to ensure Blank values. | |
| ACT XR2 (Period Based) | Y | N | SUM | N | IF(HASONEVALUE('CURRENCY CODE X-RATE 2'[Currency Code X-Rate 2]), VAR RestatedAmount = ADDCOLUMNS( SUMMARIZE('GL ANALYSIS' ,'REPORTING PERIOD'[ID],'REPORTING FROM CURRENCY 2'[Currency Code]) ,"@GLAmount",CALCULATE([ACT]) ,"@XRates" ,CALCULATE(SELECTEDVALUE('X-RATES 2'[CURRENCYRATE])) ) VAR XAmount = SUMX(RestatedAmount, [@GLAmount] * [@XRates]) RETURN XAmount ) | |
| ACT XR2 (YTD Based) | Y | Y | SUM | N | VAR CurrPeriod = MAX('REPORTING PERIOD'[Reporting Period]) RETURN IF('GL ANALYSIS MEASURES'[HasOneValuePeriod] = FALSE(), BLANK(), IF( CurrPeriod = 1, SUMX (VALUES ( COMPANY[ID] ), ([ACT]*[Conversion rate XR2]) ), SUMX (VALUES ( COMPANY[ID] ), ([ACT YTD]*[Conversion rate XR2]) - ([ACT OB]*[Conversion rate PP XR2])) ) ) // ACT X-rate (YTD Based): (ACT YTD X-rate) minus (OB * rate in previous period). Note that OB is the same as the YTD-value in the previous period. // In case no addition of ACT from previous period, this is just the effect of the same YTD value being multiplied with rate in previous period compared with the rate in this period. // NOTE: This measure can only to be presented on Reporting Period level. Can not be used on Quarter, Half year or Year level. HasOnValuePeriod is to ensure Blank values. | |
| ACT YTD (Acc Calendar) | N | VAR CurrentAccPeriodNo = MAX('ACCOUNTING PERIOD'[Accounting Period No]) VAR CurrentAccYear = MAX('ACCOUNTING PERIOD'[Accounting Year]) VAR RunningTotal = CALCULATE([ACT (Acc Calendar)], FILTER(ALL('ACCOUNTING PERIOD'), 'ACCOUNTING PERIOD'[Accounting Year] = CurrentAccYear && 'ACCOUNTING PERIOD'[Accounting Period No] <= CurrentAccPeriodNo)) RETURN IF( HASONEVALUE('ACCOUNTING PERIOD'[Accounting Year]), RunningTotal) // Uses the HASONEVALUE function to suppress display of totals that sum up multiple years. | ||||
| ACT YTD Trans | Y | Y | SUM | N | CALCULATE([ACT Trans], DATESYTD('REPORTING PERIOD'[ID])) + CALCULATE(SUM('GL ANALYSIS'[ACT_OB_PERIOD_CURR]), DATESYTD('REPORTING PERIOD'[ID])) | |
| ACT YTD XR1 (Period Based) | Y | Y | SUM | N | CALCULATE ( [ACTOBPeriodZeroXR1], DATESYTD ( 'REPORTING PERIOD'[ID] ) ) + CALCULATE ( [ACT XR1 (Period Based)], DATESYTD ( 'REPORTING PERIOD'[ID] ) ) // ACT YTD X-rate (Period Based): This YTD value includes the sum of both OB and ACT values. | |
| ACT YTD XR1 (YTD Based) | Y | Y | SUM | N | SUMX( VALUES( COMPANY[ID] ), CALCULATE( [ACT YTD] * [Conversion rate XR1] )) // ACT YTD X-rate (YTD Based): The YTD value multiplied with the current rate. // VALUES-statement to handle the multi-company scenario where companies have different conversion rates. // If ever to update this code: Heads up on verifing the results on Quarter and Year level in the multi-company scenario. | |
| ACT YTD XR2 (Period Based) | Y | Y | SUM | N | CALCULATE ( [ACTOBPeriodZeroXR2], DATESYTD ( 'REPORTING PERIOD'[ID] ) ) + CALCULATE ( [ACT XR2 (Period Based)], DATESYTD ( 'REPORTING PERIOD'[ID] ) ) // ACT YTD X-rate (Period Based): This YTD value includes the sum of both OB and ACT values. | |
| ACT YTD XR2 (YTD Based) | Y | Y | SUM | N | SUMX( VALUES( COMPANY[ID] ), CALCULATE( [ACT YTD] * [Conversion rate XR2] )) // ACT YTD X-rate (YTD Based): The YTD value multiplied with the current rate. // VALUES-statement to handle the multi-company scenario where companies have different conversion rates. // If ever to update this code: Heads up on verifing the results on Quarter and Year level in the multi-company scenario. | |
| ACTOBPeriodZero | Y | Y | SUM | N | //Calulcates the incoming balance related to [ACT], acc period 0, as defined in the underlying data source // The idea is to calculate the same value for all periods VAR UntilDate = MAX('REPORTING PERIOD'[Period Until Date]) VAR LastDatePY = MAX('REPORTING PERIOD'[Max Reporting Date PY]) VAR DiffInDays = DATEDIFF(LastDatePY, UntilDate, DAY) RETURN CALCULATE ( // go back to last data in previous reporting year + 1 [ACT OB], DATEADD('REPORTING PERIOD'[ID],-DiffInDays+1, DAY) ) | |
| ACTOBPeriodZeroXR1 | Y | Y | SUM | N | IF ( HASONEVALUE ( 'CURRENCY CODE X-RATE 1'[Currency Code X-Rate 1] ), VAR RestatedAmount = ADDCOLUMNS ( SUMMARIZE ( 'GL ANALYSIS','REPORTING PERIOD'[ID],'REPORTING FROM CURRENCY'[Currency Code] ), "@GLAmount", CALCULATE ( SUM ( 'GL ANALYSIS'[ACT_OB_PERIOD_DOM] ) ), "@XRATE", CALCULATE ( SELECTEDVALUE ( 'X-RATES 1'[PREV_YEAR_LAST_PERIOD_RATE]) ) ) VAR XAmount = SUMX ( RestatedAmount, [@GLAmount] * [@XRATE] ) RETURN XAmount ) // ACTOBPeriodZeroXR1: Calculates the OB X-rate value buy sum all OB-values and multiply with the last rate from previous year. // NOTE: If OB values exists on other periods than the start of the year, then they also is multiplied with the last rate from previous year (not previous period). | |
| ACTOBPeriodZeroXR2 | Y | Y | SUM | N | IF ( HASONEVALUE ( 'CURRENCY CODE X-RATE 2'[Currency Code X-Rate 2] ), VAR RestatedAmount = ADDCOLUMNS ( SUMMARIZE ( 'GL ANALYSIS','REPORTING PERIOD'[ID],'REPORTING FROM CURRENCY'[Currency Code] ), "@GLAmount", CALCULATE ( SUM ( 'GL ANALYSIS'[ACT_OB_PERIOD_DOM] ) ), "@XRATE", CALCULATE ( SELECTEDVALUE ( 'X-RATES 2'[PREV_YEAR_LAST_PERIOD_RATE]) ) ) VAR XAmount = SUMX ( RestatedAmount, [@GLAmount] * [@XRATE] ) RETURN XAmount ) // ACTOBPeriodZeroXR2: Calculates the OB X-rate value buy sum all OB-values and multiply with the last rate from previous year. // NOTE: If OB values exists on other periods than the start of the year, then they also is multiplied with the last rate from previous year (not previous period). | |
| BUD | Y | N | SUM | N | SUM('GL ANALYSIS'[BUD_PERIOD_DOM]) | |
| BUD OB | Y | N | SUM | N | IF(ISBLANK([BUD YTD]) , BLANK() ,([BUD YTD] - [BUD])) | |
| BUD OB Trans | Y | N | SUM | N | IF(ISBLANK([BUD YTD Trans]) , BLANK() ,([BUD YTD Trans] - [BUD Trans])) | |
| BUD OB XR1 (Period Based) | Y | N | SUM | N | IF('GL ANALYSIS MEASURES'[HasOneValuePeriod] = FALSE(), BLANK(), SUMX ( VALUES ( COMPANY[ID] ), [BUD OB]*[Conversion rate XR1])) // BUD OB X-rate (Period Based): [BUD OB] multiplied with the [rate] at each specific period. // VALUES-statement to handle the multi-company scenario where companies have different conversion rates. | |
| BUD OB XR1 (YTD Based) | Y | Y | SUM | N | VAR CurrPeriod = MAX('REPORTING PERIOD'[Reporting Period]) VAR MonthsInPeriod = MAX('REPORTING PERIOD'[Months In Period]) RETURN IF('GL ANALYSIS MEASURES'[HasOneValuePeriod] = FALSE(), BLANK() , IF ( CurrPeriod = 1, CALCULATE([BUD OB XR1 (Period Based)]), CALCULATE([BUD YTD XR1 (YTD Based)], DATEADD('REPORTING PERIOD'[ID], -MonthsInPeriod, MONTH) ) )) // BUD OB X-rate (YTD Based): The YTD value from previous period, part from period 1 in which calculation is OB*rate. // NOTE: This measure can only to be presented on Reporting Period level. Can not be used on Quarter, Half year or Year level. | |
| BUD OB XR2 (Period Based) | Y | N | SUM | N | IF('GL ANALYSIS MEASURES'[HasOneValuePeriod] = FALSE(), BLANK(), SUMX ( VALUES ( COMPANY[ID] ), [BUD OB]*[Conversion rate XR2])) // BUD OB X-rate (Period Based): [BUD OB] multiplied with the [rate] at each specific period. // VALUES-statement to handle the multi-company scenario where companies have different conversion rates. | |
| BUD OB XR2 (YTD Based) | Y | Y | SUM | N | VAR CurrPeriod = MAX('REPORTING PERIOD'[Reporting Period]) VAR MonthsInPeriod = MAX('REPORTING PERIOD'[Months In Period]) RETURN IF('GL ANALYSIS MEASURES'[HasOneValuePeriod] = FALSE(), BLANK() , IF ( CurrPeriod = 1, CALCULATE([BUD OB XR2 (Period Based)]), CALCULATE([BUD YTD XR2 (YTD Based)], DATEADD('REPORTING PERIOD'[ID], -MonthsInPeriod, MONTH) ) )) // BUD OB X-rate (YTD Based): The YTD value from previous period, part from period 1 in which calculation is OB*rate. // NOTE: This measure can only to be presented on Reporting Period level. Can not be used on Quarter, Half year or Year level. | |
| BUD PY R12 Trans | Y | Y | SUM | N | CALCULATE([BUD R12 Trans], DATEADD('REPORTING PERIOD'[ID],-12,month)) // BUD PY R12 Trans: This measure displays the last available value if presented for multiple periods, eg by quarter then the value in quarter 1 will be the value from March. | |
| BUD PY R12 XR1 (Period Based) | Y | Y | SUM | N | CALCULATE([BUD R12 XR1 (Period Based)], DATEADD('REPORTING PERIOD'[ID],-12,month)) // BUD PY R12 X-rate (Period Based): R12 X-rate from the same period previous year. // NOTE: This measure can only to be presented on Reporting Period level. Can not be used on Quarter, Half year or Year level. | |
| BUD PY R12 XR1 (YTD Based) | Y | Y | SUM | N | CALCULATE([BUD R12 XR1 (YTD Based)], DATEADD('REPORTING PERIOD'[ID],-12,month)) // BUD PY R12 X-rate (YTD Based): R12 from the same period previous year. | |
| BUD PY R12 XR2 (Period Based) | Y | Y | SUM | N | CALCULATE([BUD R12 XR2 (Period Based)], DATEADD('REPORTING PERIOD'[ID],-12,month)) // BUD PY R12 X-rate (Period Based): R12 X-rate from the same period previous year. // NOTE: This measure can only to be presented on Reporting Period level. Can not be used on Quarter, Half year or Year level. | |
| BUD PY R12 XR2 (YTD Based) | Y | Y | SUM | N | CALCULATE([BUD R12 XR2 (YTD Based)], DATEADD('REPORTING PERIOD'[ID],-12,month)) // BUD PY R12 X-rate (YTD Based): R12 from the same period previous year. | |
| BUD PY Trans | Y | Y | SUM | N | CALCULATE([BUD Trans], DATEADD('REPORTING PERIOD'[ID],-12,month)) | |
| BUD PY XR1 (Period Based) | Y | Y | SUM | N | CALCULATE([BUD XR1 (Period Based)], DATEADD('REPORTING PERIOD'[ID],-12,month)) // BUD PY X-rate (Period Based): BUD X-rate from the same reporting period previous year. | |
| BUD PY XR1 (YTD Based) | Y | Y | SUM | N | CALCULATE([BUD XR1 (YTD Based)], DATEADD('REPORTING PERIOD'[ID],-12,month)) // BUD PY X-rate (YTD Based): The value from the same period last year. | |
| BUD PY XR2 (Period Based) | Y | Y | SUM | N | CALCULATE([BUD XR2 (Period Based)], DATEADD('REPORTING PERIOD'[ID],-12,month)) // BUD PY X-rate (Period Based): BUD X-rate from the same reporting period previous year. | |
| BUD PY XR2 (YTD Based) | Y | Y | SUM | N | CALCULATE([BUD XR2 (YTD Based)], DATEADD('REPORTING PERIOD'[ID],-12,month)) // BUD PY X-rate (YTD Based): The value from the same period last year. | |
| BUD PY YTD Trans | Y | Y | SUM | N | CALCULATE([BUD YTD Trans], DATEADD('REPORTING PERIOD'[ID],-12,month)) | |
| BUD PY YTD XR1 (YTD Based) | Y | Y | SUM | N | CALCULATE([BUD YTD XR1 (YTD Based)], DATEADD('REPORTING PERIOD'[ID],-12,month)) // BUD PY YTD X-rate (YTD Based): The YTD value for the same period last year. | |
| BUD PY YTD XR2 (YTD Based) | Y | Y | SUM | N | CALCULATE([BUD YTD XR2 (YTD Based)], DATEADD('REPORTING PERIOD'[ID],-12,month)) // BUD PY YTD X-rate (YTD Based): The YTD value for the same period last year. | |
| BUD R12 Trans | Y | Y | SUM | N | VAR First_Period = CALCULATE(MAX('REPORTING PERIOD'[Reporting Year Period]), DATEADD('REPORTING PERIOD'[ID], -11, MONTH)) VAR Last_Period = MAX ( 'REPORTING PERIOD'[Reporting Year Period] ) RETURN CALCULATE ( [BUD Trans], FILTER ( ALL ( 'REPORTING PERIOD' ), 'REPORTING PERIOD'[Reporting Year Period] >= First_Period && 'REPORTING PERIOD'[Reporting Year Period] <= Last_Period ) ) // BUD R12 Trans: This measure displays the last available value if presented for multiple periods, eg by quarter then the value in quater 1 will be the value from March. | |
| BUD R12 XR1 (Period Based) | Y | Y | SUM | N | IF('GL ANALYSIS MEASURES'[HasOneValuePeriod] = FALSE(), BLANK(), [BUD XR1 (Period Based)] + CALCULATE([BUD XR1 (Period Based)], DATEADD('REPORTING PERIOD'[ID], -1, MONTH)) + CALCULATE([BUD XR1 (Period Based)], DATEADD('REPORTING PERIOD'[ID], -2, MONTH)) + CALCULATE([BUD XR1 (Period Based)], DATEADD('REPORTING PERIOD'[ID], -3, MONTH)) + CALCULATE([BUD XR1 (Period Based)], DATEADD('REPORTING PERIOD'[ID], -4, MONTH)) + CALCULATE([BUD XR1 (Period Based)], DATEADD('REPORTING PERIOD'[ID], -5, MONTH)) + CALCULATE([BUD XR1 (Period Based)], DATEADD('REPORTING PERIOD'[ID], -6, MONTH)) + CALCULATE([BUD XR1 (Period Based)], DATEADD('REPORTING PERIOD'[ID], -7, MONTH)) + CALCULATE([BUD XR1 (Period Based)], DATEADD('REPORTING PERIOD'[ID], -8, MONTH)) + CALCULATE([BUD XR1 (Period Based)], DATEADD('REPORTING PERIOD'[ID], -9, MONTH)) + CALCULATE([BUD XR1 (Period Based)], DATEADD('REPORTING PERIOD'[ID], -10, MONTH)) + CALCULATE([BUD XR1 (Period Based)], DATEADD('REPORTING PERIOD'[ID], -11, MONTH)) ) // BUD R12 X-rate (Period Based): The sum of [BUD XR1 (Period Based)] for the current month and [BUD XR1 (Period Based)] values from each of the previous 11 months. // NOTE: This measure can only to be presented on Reporting Period level. Can not be used on Quarter, Half year or Year level. | |
| BUD R12 XR1 (YTD Based) | Y | Y | SUM | N | SUMX ( VALUES ( COMPANY[ID] ), [BUD R12]*[Conversion rate XR1]) // BUD R12 X-rate (YTD Based): The R12-value multiplied with the rate at the that specific Reporting period. // VALUES-statement to handle the multi-company scenario where companies have different conversion rates. | |
| BUD R12 XR2 (Period Based) | Y | Y | SUM | N | IF('GL ANALYSIS MEASURES'[HasOneValuePeriod] = FALSE(), BLANK(), [BUD XR2 (Period Based)] + CALCULATE([BUD XR2 (Period Based)], DATEADD('REPORTING PERIOD'[ID], -1, MONTH)) + CALCULATE([BUD XR2 (Period Based)], DATEADD('REPORTING PERIOD'[ID], -2, MONTH)) + CALCULATE([BUD XR2 (Period Based)], DATEADD('REPORTING PERIOD'[ID], -3, MONTH)) + CALCULATE([BUD XR2 (Period Based)], DATEADD('REPORTING PERIOD'[ID], -4, MONTH)) + CALCULATE([BUD XR2 (Period Based)], DATEADD('REPORTING PERIOD'[ID], -5, MONTH)) + CALCULATE([BUD XR2 (Period Based)], DATEADD('REPORTING PERIOD'[ID], -6, MONTH)) + CALCULATE([BUD XR2 (Period Based)], DATEADD('REPORTING PERIOD'[ID], -7, MONTH)) + CALCULATE([BUD XR2 (Period Based)], DATEADD('REPORTING PERIOD'[ID], -8, MONTH)) + CALCULATE([BUD XR2 (Period Based)], DATEADD('REPORTING PERIOD'[ID], -9, MONTH)) + CALCULATE([BUD XR2 (Period Based)], DATEADD('REPORTING PERIOD'[ID], -10, MONTH)) + CALCULATE([BUD XR2 (Period Based)], DATEADD('REPORTING PERIOD'[ID], -11, MONTH)) ) // BUD R12 X-rate (Period Based): The sum of [BUD XR2 (Period Based)] for the current month and [BUD XR2 (Period Based)] values from each of the previous 11 months. // NOTE: This measure can only to be presented on Reporting Period level. Can not be used on Quarter, Half year or Year level. | |
| BUD R12 XR2 (YTD Based) | Y | Y | SUM | N | SUMX ( VALUES ( COMPANY[ID] ), [BUD R12]*[Conversion rate XR2]) // BUD R12 X-rate (YTD Based): The R12-value multiplied with the rate at the that specific Reporting period. // VALUES-statement to handle the multi-company scenario where companies have different conversion rates. | |
| BUD Trans | Y | N | SUM | N | SUM('GL ANALYSIS'[BUD_PERIOD_CURR]) | |
| BUD XR1 (Period Based) | Y | N | SUM | N | IF(HASONEVALUE('CURRENCY CODE X-RATE 1'[Currency Code X-Rate 1]), VAR RestatedAmount = ADDCOLUMNS( SUMMARIZE('GL ANALYSIS' ,'REPORTING PERIOD'[ID],'REPORTING FROM CURRENCY'[Currency Code]) ,"@GLAmount",CALCULATE([BUD]) ,"@XRates" ,CALCULATE(SELECTEDVALUE('X-RATES 1'[CURRENCYRATE])) ) VAR XAmount = SUMX(RestatedAmount, [@GLAmount] * [@XRates]) RETURN XAmount ) | |
| BUD XR1 (YTD Based) | Y | Y | SUM | N | VAR CurrPeriod = MAX('REPORTING PERIOD'[Reporting Period]) RETURN IF('GL ANALYSIS MEASURES'[HasOneValuePeriod] = FALSE(), BLANK(), IF( CurrPeriod = 1, SUMX (VALUES ( COMPANY[ID] ), ([BUD]*[Conversion rate XR1]) ), SUMX (VALUES ( COMPANY[ID] ), ([BUD YTD]*[Conversion rate XR1]) - ([BUD OB]*[Conversion rate PP XR1])) ) ) // BUD X-rate (YTD Based): (BUD YTD X-rate) minus (OB * rate in previous period). Note that OB is the same as the YTD-value in the previous period. // In case no addition of BUD from previous period, this is just the effect of the same YTD value being multiplied with rate in previous period compared with the rate in this period. // NOTE: This measure can only to be presented on Reporting Period level. Can not be used on Quarter, Half year or Year level. HasOnValuePeriod is to ensure Blank values. | |
| BUD XR2 (Period Based) | Y | N | SUM | N | IF(HASONEVALUE('CURRENCY CODE X-RATE 2'[Currency Code X-Rate 2]), VAR RestatedAmount = ADDCOLUMNS( SUMMARIZE('GL ANALYSIS' ,'REPORTING PERIOD'[ID],'REPORTING FROM CURRENCY 2'[Currency Code]) ,"@GLAmount",CALCULATE([BUD]) ,"@XRates" ,CALCULATE(SELECTEDVALUE('X-RATES 2'[CURRENCYRATE])) ) VAR XAmount = SUMX(RestatedAmount, [@GLAmount] * [@XRates]) RETURN XAmount ) | |
| BUD XR2 (YTD Based) | Y | Y | SUM | N | VAR CurrPeriod = MAX('REPORTING PERIOD'[Reporting Period]) RETURN IF('GL ANALYSIS MEASURES'[HasOneValuePeriod] = FALSE(), BLANK(), IF( CurrPeriod = 1, SUMX (VALUES ( COMPANY[ID] ), ([BUD]*[Conversion rate XR2]) ), SUMX (VALUES ( COMPANY[ID] ), ([BUD YTD]*[Conversion rate XR2]) - ([BUD OB]*[Conversion rate PP XR2])) ) ) // BUD X-rate (YTD Based): (BUD YTD X-rate) minus (OB * rate in previous period). Note that OB is the same as the YTD-value in the previous period. // In case no addition of BUD from previous period, this is just the effect of the same YTD value being multiplied with rate in previous period compared with the rate in this period. // NOTE: This measure can only to be presented on Reporting Period level. Can not be used on Quarter, Half year or Year level. HasOnValuePeriod is to ensure Blank values. | |
| BUD YTD Trans | Y | Y | SUM | N | CALCULATE([BUD Trans], DATESYTD('REPORTING PERIOD'[ID])) + CALCULATE(SUM('GL ANALYSIS'[BUD_OB_PERIOD_CURR]), DATESYTD('REPORTING PERIOD'[ID])) | |
| BUD YTD XR1 (Period Based) | Y | Y | SUM | N | CALCULATE ( [BUDOBPeriodZeroXR1], DATESYTD ( 'REPORTING PERIOD'[ID] ) ) + CALCULATE ( [BUD XR1 (Period Based)], DATESYTD ( 'REPORTING PERIOD'[ID] ) ) // BUD YTD X-rate (Period Based): This YTD value includes the sum of both OB and BUD values. | |
| BUD YTD XR1 (YTD Based) | Y | Y | SUM | N | SUMX( VALUES( COMPANY[ID] ), CALCULATE( [BUD YTD] * [Conversion rate XR1] )) // BUD YTD X-rate (YTD Based): The YTD value multiplied with the current rate. // VALUES-statement to handle the multi-company scenario where companies have different conversion rates. // If ever to update this code: Heads up on verifing the results on Quarter and Year level in the multi-company scenario. | |
| BUD YTD XR2 (Period Based) | Y | Y | SUM | N | CALCULATE ( [BUDOBPeriodZeroXR2], DATESYTD ( 'REPORTING PERIOD'[ID] ) ) + CALCULATE ( [BUD XR2 (Period Based)], DATESYTD ( 'REPORTING PERIOD'[ID] ) ) // BUD YTD X-rate (Period Based): This YTD value includes the sum of both OB and BUD values. | |
| BUD YTD XR2 (YTD Based) | Y | Y | SUM | N | SUMX( VALUES( COMPANY[ID] ), CALCULATE( [BUD YTD] * [Conversion rate XR2] )) // BUD YTD X-rate (YTD Based): The YTD value multiplied with the current rate. // VALUES-statement to handle the multi-company scenario where companies have different conversion rates. // If ever to update this code: Heads up on verifing the results on Quarter and Year level in the multi-company scenario. | |
| BUDOBPeriodZero | Y | Y | SUM | N | //Calulcates the incoming balance related to [BUD], acc period 0, as defined in the underlying data source // The idea is to calculate the same value for all periods VAR UntilDate = MAX('REPORTING PERIOD'[Period Until Date]) VAR LastDatePY = MAX('REPORTING PERIOD'[Max Reporting Date PY]) VAR DiffInDays = DATEDIFF(LastDatePY, UntilDate, DAY) RETURN CALCULATE ( // go back to last data in previous reporting year + 1 [BUD OB], DATEADD('REPORTING PERIOD'[ID],-DiffInDays+1, DAY) ) | |
| BUDOBPeriodZeroXR1 | Y | Y | SUM | N | IF ( HASONEVALUE ( 'CURRENCY CODE X-RATE 1'[Currency Code X-Rate 1] ), VAR RestatedAmount = ADDCOLUMNS ( SUMMARIZE ( 'GL ANALYSIS','REPORTING PERIOD'[ID],'REPORTING FROM CURRENCY'[Currency Code] ), "@GLAmount", CALCULATE ( SUM ( 'GL ANALYSIS'[BUD_OB_PERIOD_DOM] ) ), "@XRATE", CALCULATE ( SELECTEDVALUE ( 'X-RATES 1'[PREV_YEAR_LAST_PERIOD_RATE]) ) ) VAR XAmount = SUMX ( RestatedAmount, [@GLAmount] * [@XRATE] ) RETURN XAmount ) // BUDOBPeriodZeroXR1: Calculates the OB X-rate value buy sum all OB-values and multiply with the last rate from previous year. // NOTE: If OB values exists on other periods than the start of the year, then they also is multiplied with the last rate from previous year (not previous period). | |
| BUDOBPeriodZeroXR2 | Y | Y | SUM | N | IF ( HASONEVALUE ( 'CURRENCY CODE X-RATE 2'[Currency Code X-Rate 2] ), VAR RestatedAmount = ADDCOLUMNS ( SUMMARIZE ( 'GL ANALYSIS','REPORTING PERIOD'[ID],'REPORTING FROM CURRENCY'[Currency Code] ), "@GLAmount", CALCULATE ( SUM ( 'GL ANALYSIS'[BUD_OB_PERIOD_DOM] ) ), "@XRATE", CALCULATE ( SELECTEDVALUE ( 'X-RATES 2'[PREV_YEAR_LAST_PERIOD_RATE]) ) ) VAR XAmount = SUMX ( RestatedAmount, [@GLAmount] * [@XRATE] ) RETURN XAmount ) // BUDOBPeriodZeroXR2: Calculates the OB X-rate value buy sum all OB-values and multiply with the last rate from previous year. // NOTE: If OB values exists on other periods than the start of the year, then they also is multiplied with the last rate from previous year (not previous period). | |
| CFC | Y | N | SUM | N | SUM('GL ANALYSIS'[CFC_PERIOD_DOM]) | |
| CFC OB | Y | N | SUM | N | IF(ISBLANK([CFC YTD]) , BLANK() ,([CFC YTD] - [CFC])) | |
| CFC OB Trans | Y | N | SUM | N | IF(ISBLANK([CFC YTD Trans]) , BLANK() ,([CFC YTD Trans] - [CFC Trans])) | |
| CFC OB XR1 (Period Based) | Y | N | SUM | N | IF('GL ANALYSIS MEASURES'[HasOneValuePeriod] = FALSE(), BLANK(), SUMX ( VALUES ( COMPANY[ID] ), [CFC OB]*[Conversion rate XR1])) // CFC OB X-rate (Period Based): [CFC OB] multiplied with the [rate] at each specific period. // VALUES-statement to handle the multi-company scenario where companies have different conversion rates. | |
| CFC OB XR1 (YTD Based) | Y | Y | SUM | N | VAR CurrPeriod = MAX('REPORTING PERIOD'[Reporting Period]) VAR MonthsInPeriod = MAX('REPORTING PERIOD'[Months In Period]) RETURN IF('GL ANALYSIS MEASURES'[HasOneValuePeriod] = FALSE(), BLANK() , IF ( CurrPeriod = 1, CALCULATE([CFC OB XR1 (Period Based)]), CALCULATE([CFC YTD XR1 (YTD Based)], DATEADD('REPORTING PERIOD'[ID], -MonthsInPeriod, MONTH) ) )) // CFC OB X-rate (YTD Based): The YTD value from previous period, part from period 1 in which calculation is OB*rate. // NOTE: This measure can only to be presented on Reporting Period level. Can not be used on Quarter, Half year or Year level. | |
| CFC OB XR2 (Period Based) | Y | N | SUM | N | IF('GL ANALYSIS MEASURES'[HasOneValuePeriod] = FALSE(), BLANK(), SUMX ( VALUES ( COMPANY[ID] ), [CFC OB]*[Conversion rate XR2])) // CFC OB X-rate (Period Based): [CFC OB] multiplied with the [rate] at each specific period. // VALUES-statement to handle the multi-company scenario where companies have different conversion rates. | |
| CFC OB XR2 (YTD Based) | Y | Y | SUM | N | VAR CurrPeriod = MAX('REPORTING PERIOD'[Reporting Period]) VAR MonthsInPeriod = MAX('REPORTING PERIOD'[Months In Period]) RETURN IF('GL ANALYSIS MEASURES'[HasOneValuePeriod] = FALSE(), BLANK() , IF ( CurrPeriod = 1, CALCULATE([CFC OB XR2 (Period Based)]), CALCULATE([CFC YTD XR2 (YTD Based)], DATEADD('REPORTING PERIOD'[ID], -MonthsInPeriod, MONTH) ) )) // CFC OB X-rate (YTD Based): The YTD value from previous period, part from period 1 in which calculation is OB*rate. // NOTE: This measure can only to be presented on Reporting Period level. Can not be used on Quarter, Half year or Year level. | |
| CFC R12 Trans | Y | Y | SUM | N | VAR First_Period = CALCULATE(MAX('REPORTING PERIOD'[Reporting Year Period]), DATEADD('REPORTING PERIOD'[ID], -11, MONTH)) VAR Last_Period = MAX ( 'REPORTING PERIOD'[Reporting Year Period] ) RETURN CALCULATE ( [CFC Trans], FILTER ( ALL ( 'REPORTING PERIOD' ), 'REPORTING PERIOD'[Reporting Year Period] >= First_Period && 'REPORTING PERIOD'[Reporting Year Period] <= Last_Period ) ) // CFC R12 Trans: This measure displays the last available value if presented for multiple periods, eg by quarter then the value in quater 1 will be the value from March. | |
| CFC R12 XR1 (Period Based) | Y | Y | SUM | N | IF('GL ANALYSIS MEASURES'[HasOneValuePeriod] = FALSE(), BLANK(), [CFC XR1 (Period Based)] + CALCULATE([CFC XR1 (Period Based)], DATEADD('REPORTING PERIOD'[ID], -1, MONTH)) + CALCULATE([CFC XR1 (Period Based)], DATEADD('REPORTING PERIOD'[ID], -2, MONTH)) + CALCULATE([CFC XR1 (Period Based)], DATEADD('REPORTING PERIOD'[ID], -3, MONTH)) + CALCULATE([CFC XR1 (Period Based)], DATEADD('REPORTING PERIOD'[ID], -4, MONTH)) + CALCULATE([CFC XR1 (Period Based)], DATEADD('REPORTING PERIOD'[ID], -5, MONTH)) + CALCULATE([CFC XR1 (Period Based)], DATEADD('REPORTING PERIOD'[ID], -6, MONTH)) + CALCULATE([CFC XR1 (Period Based)], DATEADD('REPORTING PERIOD'[ID], -7, MONTH)) + CALCULATE([CFC XR1 (Period Based)], DATEADD('REPORTING PERIOD'[ID], -8, MONTH)) + CALCULATE([CFC XR1 (Period Based)], DATEADD('REPORTING PERIOD'[ID], -9, MONTH)) + CALCULATE([CFC XR1 (Period Based)], DATEADD('REPORTING PERIOD'[ID], -10, MONTH)) + CALCULATE([CFC XR1 (Period Based)], DATEADD('REPORTING PERIOD'[ID], -11, MONTH)) ) // CFC R12 X-rate (Period Based): The sum of [CFC XR1 (Period Based)] for the current month and [CFC XR1 (Period Based)] values from each of the previous 11 months. // NOTE: This measure can only to be presented on Reporting Period level. Can not be used on Quarter, Half year or Year level. | |
| CFC R12 XR1 (YTD Based) | Y | Y | SUM | N | SUMX ( VALUES ( COMPANY[ID] ), [CFC R12]*[Conversion rate XR1]) // CFC R12 X-rate (YTD Based): The R12-value multiplied with the rate at the that specific Reporting period. // VALUES-statement to handle the multi-company scenario where companies have different conversion rates. | |
| CFC R12 XR2 (Period Based) | Y | Y | SUM | N | IF('GL ANALYSIS MEASURES'[HasOneValuePeriod] = FALSE(), BLANK(), [CFC XR2 (Period Based)] + CALCULATE([CFC XR2 (Period Based)], DATEADD('REPORTING PERIOD'[ID], -1, MONTH)) + CALCULATE([CFC XR2 (Period Based)], DATEADD('REPORTING PERIOD'[ID], -2, MONTH)) + CALCULATE([CFC XR2 (Period Based)], DATEADD('REPORTING PERIOD'[ID], -3, MONTH)) + CALCULATE([CFC XR2 (Period Based)], DATEADD('REPORTING PERIOD'[ID], -4, MONTH)) + CALCULATE([CFC XR2 (Period Based)], DATEADD('REPORTING PERIOD'[ID], -5, MONTH)) + CALCULATE([CFC XR2 (Period Based)], DATEADD('REPORTING PERIOD'[ID], -6, MONTH)) + CALCULATE([CFC XR2 (Period Based)], DATEADD('REPORTING PERIOD'[ID], -7, MONTH)) + CALCULATE([CFC XR2 (Period Based)], DATEADD('REPORTING PERIOD'[ID], -8, MONTH)) + CALCULATE([CFC XR2 (Period Based)], DATEADD('REPORTING PERIOD'[ID], -9, MONTH)) + CALCULATE([CFC XR2 (Period Based)], DATEADD('REPORTING PERIOD'[ID], -10, MONTH)) + CALCULATE([CFC XR2 (Period Based)], DATEADD('REPORTING PERIOD'[ID], -11, MONTH)) ) // CFC R12 X-rate (Period Based): The sum of [CFC XR2 (Period Based)] for the current month and [CFC XR2 (Period Based)] values from each of the previous 11 months. // NOTE: This measure can only to be presented on Reporting Period level. Can not be used on Quarter, Half year or Year level. | |
| CFC R12 XR2 (YTD Based) | Y | Y | SUM | N | SUMX ( VALUES ( COMPANY[ID] ), [CFC R12]*[Conversion rate XR2]) // CFC R12 X-rate (YTD Based): The R12-value multiplied with the rate at the that specific Reporting period. // VALUES-statement to handle the multi-company scenario where companies have different conversion rates. | |
| CFC Trans | Y | N | SUM | N | SUM('GL ANALYSIS'[CFC_PERIOD_CURR]) | |
| CFC XR1 (Period Based) | Y | N | SUM | N | IF(HASONEVALUE('CURRENCY CODE X-RATE 1'[Currency Code X-Rate 1]), VAR RestatedAmount = ADDCOLUMNS( SUMMARIZE('GL ANALYSIS' ,'REPORTING PERIOD'[ID],'REPORTING FROM CURRENCY'[Currency Code]) ,"@GLAmount",CALCULATE([CFC]) ,"@XRates" ,CALCULATE(SELECTEDVALUE('X-RATES 1'[CURRENCYRATE])) ) VAR XAmount = SUMX(RestatedAmount, [@GLAmount] * [@XRates]) RETURN XAmount ) | |
| CFC XR1 (YTD Based) | Y | Y | SUM | N | VAR CurrPeriod = MAX('REPORTING PERIOD'[Reporting Period]) RETURN IF('GL ANALYSIS MEASURES'[HasOneValuePeriod] = FALSE(), BLANK(), IF( CurrPeriod = 1, SUMX (VALUES ( COMPANY[ID] ), ([CFC]*[Conversion rate XR1]) ), SUMX (VALUES ( COMPANY[ID] ), ([CFC YTD]*[Conversion rate XR1]) - ([CFC OB]*[Conversion rate PP XR1])) ) ) // CFC X-rate (YTD Based): (CFC YTD X-rate) minus (OB * rate in previous period). Note that OB is the same as the YTD-value in the previous period. // In case no addition of CFC from previous period, this is just the effect of the same YTD value being multiplied with rate in previous period compared with the rate in this period. // NOTE: This measure can only to be presented on Reporting Period level. Can not be used on Quarter, Half year or Year level. HasOnValuePeriod is to ensure Blank values. | |
| CFC XR2 (Period Based) | Y | N | SUM | N | IF(HASONEVALUE('CURRENCY CODE X-RATE 2'[Currency Code X-Rate 2]), VAR RestatedAmount = ADDCOLUMNS( SUMMARIZE('GL ANALYSIS' ,'REPORTING PERIOD'[ID],'REPORTING FROM CURRENCY 2'[Currency Code]) ,"@GLAmount",CALCULATE([CFC]) ,"@XRates" ,CALCULATE(SELECTEDVALUE('X-RATES 2'[CURRENCYRATE])) ) VAR XAmount = SUMX(RestatedAmount, [@GLAmount] * [@XRates]) RETURN XAmount ) | |
| CFC XR2 (YTD Based) | Y | Y | SUM | N | VAR CurrPeriod = MAX('REPORTING PERIOD'[Reporting Period]) RETURN IF('GL ANALYSIS MEASURES'[HasOneValuePeriod] = FALSE(), BLANK(), IF( CurrPeriod = 1, SUMX (VALUES ( COMPANY[ID] ), ([CFC]*[Conversion rate XR2]) ), SUMX (VALUES ( COMPANY[ID] ), ([CFC YTD]*[Conversion rate XR2]) - ([CFC OB]*[Conversion rate PP XR2])) ) ) // CFC X-rate (YTD Based): (CFC YTD X-rate) minus (OB * rate in previous period). Note that OB is the same as the YTD-value in the previous period. // In case no addition of CFC from previous period, this is just the effect of the same YTD value being multiplied with rate in previous period compared with the rate in this period. // NOTE: This measure can only to be presented on Reporting Period level. Can not be used on Quarter, Half year or Year level. HasOnValuePeriod is to ensure Blank values. | |
| CFC YTD Trans | Y | Y | SUM | N | CALCULATE([CFC Trans], DATESYTD('REPORTING PERIOD'[ID])) + CALCULATE(SUM('GL ANALYSIS'[CFC_OB_PERIOD_CURR]), DATESYTD('REPORTING PERIOD'[ID])) | |
| CFC YTD XR1 (Period Based) | Y | Y | SUM | N | CALCULATE ( [CFCOBPeriodZeroXR1], DATESYTD ( 'REPORTING PERIOD'[ID] ) ) + CALCULATE ( [CFC XR1 (Period Based)], DATESYTD ( 'REPORTING PERIOD'[ID] ) ) // CFC YTD X-rate (Period Based): This YTD value includes the sum of both OB and CFC values. | |
| CFC YTD XR1 (YTD Based) | Y | Y | SUM | N | SUMX( VALUES( COMPANY[ID] ), CALCULATE( [CFC YTD] * [Conversion rate XR1] )) // CFC YTD X-rate (YTD Based): The YTD value multiplied with the current rate. // VALUES-statement to handle the multi-company scenario where companies have different conversion rates. // If ever to update this code: Heads up on verifing the results on Quarter and Year level in the multi-company scenario. | |
| CFC YTD XR2 (Period Based) | Y | Y | SUM | N | CALCULATE ( [CFCOBPeriodZeroXR2], DATESYTD ( 'REPORTING PERIOD'[ID] ) ) + CALCULATE ( [CFC XR2 (Period Based)], DATESYTD ( 'REPORTING PERIOD'[ID] ) ) // CFC YTD X-rate (Period Based): This YTD value includes the sum of both OB and CFC values. | |
| CFC YTD XR2 (YTD Based) | Y | Y | SUM | N | SUMX( VALUES( COMPANY[ID] ), CALCULATE( [CFC YTD] * [Conversion rate XR2] )) // CFC YTD X-rate (YTD Based): The YTD value multiplied with the current rate. // VALUES-statement to handle the multi-company scenario where companies have different conversion rates. // If ever to update this code: Heads up on verifing the results on Quarter and Year level in the multi-company scenario. | |
| CFCOBPeriodZero | Y | Y | SUM | N | //Calulcates the incoming balance related to [CFC], acc period 0, as defined in the underlying data source // The idea is to calculate the same value for all periods VAR UntilDate = MAX('REPORTING PERIOD'[Period Until Date]) VAR LastDatePY = MAX('REPORTING PERIOD'[Max Reporting Date PY]) VAR DiffInDays = DATEDIFF(LastDatePY, UntilDate, DAY) RETURN CALCULATE ( // go back to last data in previous reporting year + 1 [CFC OB], DATEADD('REPORTING PERIOD'[ID],-DiffInDays+1, DAY) ) | |
| CFCOBPeriodZeroXR1 | Y | Y | SUM | N | IF ( HASONEVALUE ( 'CURRENCY CODE X-RATE 1'[Currency Code X-Rate 1] ), VAR RestatedAmount = ADDCOLUMNS ( SUMMARIZE ( 'GL ANALYSIS','REPORTING PERIOD'[ID],'REPORTING FROM CURRENCY'[Currency Code] ), "@GLAmount", CALCULATE ( SUM ( 'GL ANALYSIS'[CFC_OB_PERIOD_DOM] ) ), "@XRATE", CALCULATE ( SELECTEDVALUE ( 'X-RATES 1'[PREV_YEAR_LAST_PERIOD_RATE]) ) ) VAR XAmount = SUMX ( RestatedAmount, [@GLAmount] * [@XRATE] ) RETURN XAmount ) // CFCOBPeriodZeroXR1: Calculates the OB X-rate value buy sum all OB-values and multiply with the last rate from previous year. // NOTE: If OB values exists on other periods than the start of the year, then they also is multiplied with the last rate from previous year (not previous period). | |
| CFCOBPeriodZeroXR2 | Y | Y | SUM | N | IF ( HASONEVALUE ( 'CURRENCY CODE X-RATE 2'[Currency Code X-Rate 2] ), VAR RestatedAmount = ADDCOLUMNS ( SUMMARIZE ( 'GL ANALYSIS','REPORTING PERIOD'[ID],'REPORTING FROM CURRENCY'[Currency Code] ), "@GLAmount", CALCULATE ( SUM ( 'GL ANALYSIS'[CFC_OB_PERIOD_DOM] ) ), "@XRATE", CALCULATE ( SELECTEDVALUE ( 'X-RATES 2'[PREV_YEAR_LAST_PERIOD_RATE]) ) ) VAR XAmount = SUMX ( RestatedAmount, [@GLAmount] * [@XRATE] ) RETURN XAmount ) // CFCOBPeriodZeroXR2: Calculates the OB X-rate value buy sum all OB-values and multiply with the last rate from previous year. // NOTE: If OB values exists on other periods than the start of the year, then they also is multiplied with the last rate from previous year (not previous period). | |
| Conversion rate PP XR1 | N | CALCULATE([Conversion rate XR1], DATEADD('REPORTING PERIOD'[ID],-1,month)) // Conversion rate XR1 PP: Rate for the previous reporting period. // NOTE: This measure requires that companies never change their "company_base_currency"-value. | ||||
| Conversion rate PP XR2 | N | CALCULATE([Conversion rate XR2], DATEADD('REPORTING PERIOD'[ID],-1,month)) // Conversion rate XR2 PP: Rate for the previous reporting period. // NOTE: This measure requires that companies never change their "company_base_currency"-value. | ||||
| Conversion rate XR1 | N | IF ( HASONEVALUE ( 'CURRENCY CODE X-RATE 1'[Currency Code X-Rate 1] ), VAR SelectedXrateType = SELECTEDVALUE('RATE TYPE X-RATE 1'[Rate Type Code X-Rate 1]) VAR SelectedToCurrency = SELECTEDVALUE ('CURRENCY CODE X-RATE 1'[Currency Code X-Rate 1] ) VAR selected_company = SELECTEDVALUE(COMPANY[ID]) VAR base_currency = CALCULATE( SELECTEDVALUE('GL ANALYSIS'[company_base_currency]), COMPANY[ID] = selected_company, ALL('REPORTING PERIOD') ) VAR LastReportingPeriodWithRate = CALCULATE( MAX('REPORTING PERIOD'[ID]), FILTER( 'X-RATES 1', 'X-RATES 1'[CURRENCY_RATE_TYPE_KEY] = SelectedXrateType && 'X-RATES 1'[TOCURRENCY] = SelectedToCurrency && 'X-RATES 1'[FROMCURRENCY] = base_currency && NOT(ISBLANK('X-RATES 1'[CURRENCYRATE])) ) ) VAR ConversionRate = CALCULATE( MAX('X-RATES 1'[CURRENCYRATE]), 'X-RATES 1'[CURRENCY_RATE_TYPE_KEY] = SelectedXrateType, 'X-RATES 1'[TOCURRENCY] = SelectedToCurrency, 'X-RATES 1'[FROMCURRENCY] = base_currency, 'REPORTING PERIOD'[ID] = LastReportingPeriodWithRate ) RETURN ConversionRate ) // Conversion rate XR1: Rate for the current reporting period. // NOTE: This measure requires that companies never change their "company_base_currency"-value. | ||||
| Conversion rate XR2 | N | IF ( HASONEVALUE ( 'CURRENCY CODE X-RATE 2'[Currency Code X-Rate 2] ), VAR SelectedXrateType = SELECTEDVALUE('RATE TYPE X-RATE 2'[Rate Type Code X-Rate 2]) VAR SelectedToCurrency = SELECTEDVALUE ('CURRENCY CODE X-RATE 2'[Currency Code X-Rate 2] ) VAR selected_company = SELECTEDVALUE(COMPANY[ID]) VAR base_currency = CALCULATE( SELECTEDVALUE('GL ANALYSIS'[company_base_currency]), COMPANY[ID] = selected_company, ALL('REPORTING PERIOD') ) VAR LastReportingPeriodWithRate = CALCULATE( MAX('REPORTING PERIOD'[ID]), FILTER( 'X-RATES 2', 'X-RATES 2'[CURRENCY_RATE_TYPE_KEY] = SelectedXrateType && 'X-RATES 2'[TOCURRENCY] = SelectedToCurrency && 'X-RATES 2'[FROMCURRENCY] = base_currency && NOT(ISBLANK('X-RATES 2'[CURRENCYRATE])) ) ) VAR ConversionRate = CALCULATE( MAX('X-RATES 2'[CURRENCYRATE]), 'X-RATES 2'[CURRENCY_RATE_TYPE_KEY] = SelectedXrateType, 'X-RATES 2'[TOCURRENCY] = SelectedToCurrency, 'X-RATES 2'[FROMCURRENCY] = base_currency, 'REPORTING PERIOD'[ID] = LastReportingPeriodWithRate ) RETURN ConversionRate ) // Conversion rate XR2: Rate for the current reporting period. // NOTE: This measure requires that companies never change their "company_base_currency"-value. | ||||
| HasOneValuePeriod | Y | N | N/A | Y | HASONEVALUE('REPORTING PERIOD'[Reporting Period]) | |
| NFC | Y | N | SUM | N | SUM('GL ANALYSIS'[NFC_PERIOD_DOM]) | |
| NFC OB | Y | N | SUM | N | IF(ISBLANK([NFC YTD]) , BLANK() ,([NFC YTD] - [NFC])) | |
| NFC OB Trans | Y | N | SUM | N | IF(ISBLANK([NFC YTD Trans]) , BLANK() ,([NFC YTD Trans] - [NFC Trans])) | |
| NFC OB XR1 (Period Based) | Y | N | SUM | N | IF('GL ANALYSIS MEASURES'[HasOneValuePeriod] = FALSE(), BLANK(), SUMX ( VALUES ( COMPANY[ID] ), [NFC OB]*[Conversion rate XR1])) // NFC OB X-rate (Period Based): [NFC OB] multiplied with the [rate] at each specific period. // VALUES-statement to handle the multi-company scenario where companies have different conversion rates. | |
| NFC OB XR1 (YTD Based) | Y | Y | SUM | N | VAR CurrPeriod = MAX('REPORTING PERIOD'[Reporting Period]) VAR MonthsInPeriod = MAX('REPORTING PERIOD'[Months In Period]) RETURN IF('GL ANALYSIS MEASURES'[HasOneValuePeriod] = FALSE(), BLANK() , IF ( CurrPeriod = 1, CALCULATE([NFC OB XR1 (Period Based)]), CALCULATE([NFC YTD XR1 (YTD Based)], DATEADD('REPORTING PERIOD'[ID], -MonthsInPeriod, MONTH) ) )) // NFC OB X-rate (YTD Based): The YTD value from previous period, part from period 1 in which calculation is OB*rate. // NOTE: This measure can only to be presented on Reporting Period level. Can not be used on Quarter, Half year or Year level. | |
| NFC OB XR2 (Period Based) | Y | N | SUM | N | IF('GL ANALYSIS MEASURES'[HasOneValuePeriod] = FALSE(), BLANK(), SUMX ( VALUES ( COMPANY[ID] ), [NFC OB]*[Conversion rate XR2])) // NFC OB X-rate (Period Based): [NFC OB] multiplied with the [rate] at each specific period. // VALUES-statement to handle the multi-company scenario where companies have different conversion rates. | |
| NFC OB XR2 (YTD Based) | Y | Y | SUM | N | VAR CurrPeriod = MAX('REPORTING PERIOD'[Reporting Period]) VAR MonthsInPeriod = MAX('REPORTING PERIOD'[Months In Period]) RETURN IF('GL ANALYSIS MEASURES'[HasOneValuePeriod] = FALSE(), BLANK() , IF ( CurrPeriod = 1, CALCULATE([NFC OB XR2 (Period Based)]), CALCULATE([NFC YTD XR2 (YTD Based)], DATEADD('REPORTING PERIOD'[ID], -MonthsInPeriod, MONTH) ) )) // NFC OB X-rate (YTD Based): The YTD value from previous period, part from period 1 in which calculation is OB*rate. // NOTE: This measure can only to be presented on Reporting Period level. Can not be used on Quarter, Half year or Year level. | |
| NFC R12 Trans | Y | Y | SUM | N | VAR First_Period = CALCULATE(MAX('REPORTING PERIOD'[Reporting Year Period]), DATEADD('REPORTING PERIOD'[ID], -11, MONTH)) VAR Last_Period = MAX ( 'REPORTING PERIOD'[Reporting Year Period] ) RETURN CALCULATE ( [NFC Trans], FILTER ( ALL ( 'REPORTING PERIOD' ), 'REPORTING PERIOD'[Reporting Year Period] >= First_Period && 'REPORTING PERIOD'[Reporting Year Period] <= Last_Period ) ) // NFC R12 Trans: This measure displays the last available value if presented for multiple periods, eg by quarter then the value in quater 1 will be the value from March. | |
| NFC R12 XR1 (Period Based) | Y | Y | SUM | N | IF('GL ANALYSIS MEASURES'[HasOneValuePeriod] = FALSE(), BLANK(), [NFC XR1 (Period Based)] + CALCULATE([NFC XR1 (Period Based)], DATEADD('REPORTING PERIOD'[ID], -1, MONTH)) + CALCULATE([NFC XR1 (Period Based)], DATEADD('REPORTING PERIOD'[ID], -2, MONTH)) + CALCULATE([NFC XR1 (Period Based)], DATEADD('REPORTING PERIOD'[ID], -3, MONTH)) + CALCULATE([NFC XR1 (Period Based)], DATEADD('REPORTING PERIOD'[ID], -4, MONTH)) + CALCULATE([NFC XR1 (Period Based)], DATEADD('REPORTING PERIOD'[ID], -5, MONTH)) + CALCULATE([NFC XR1 (Period Based)], DATEADD('REPORTING PERIOD'[ID], -6, MONTH)) + CALCULATE([NFC XR1 (Period Based)], DATEADD('REPORTING PERIOD'[ID], -7, MONTH)) + CALCULATE([NFC XR1 (Period Based)], DATEADD('REPORTING PERIOD'[ID], -8, MONTH)) + CALCULATE([NFC XR1 (Period Based)], DATEADD('REPORTING PERIOD'[ID], -9, MONTH)) + CALCULATE([NFC XR1 (Period Based)], DATEADD('REPORTING PERIOD'[ID], -10, MONTH)) + CALCULATE([NFC XR1 (Period Based)], DATEADD('REPORTING PERIOD'[ID], -11, MONTH)) ) // NFC R12 X-rate (Period Based): The sum of [NFC XR1 (Period Based)] for the current month and [NFC XR1 (Period Based)] values from each of the previous 11 months. // NOTE: This measure can only to be presented on Reporting Period level. Can not be used on Quarter, Half year or Year level. | |
| NFC R12 XR1 (YTD Based) | Y | Y | SUM | N | SUMX ( VALUES ( COMPANY[ID] ), [NFC R12]*[Conversion rate XR1]) // NFC R12 X-rate (YTD Based): The R12-value multiplied with the rate at the that specific Reporting period. // VALUES-statement to handle the multi-company scenario where companies have different conversion rates. | |
| NFC R12 XR2 (Period Based) | Y | Y | SUM | N | IF('GL ANALYSIS MEASURES'[HasOneValuePeriod] = FALSE(), BLANK(), [NFC XR2 (Period Based)] + CALCULATE([NFC XR2 (Period Based)], DATEADD('REPORTING PERIOD'[ID], -1, MONTH)) + CALCULATE([NFC XR2 (Period Based)], DATEADD('REPORTING PERIOD'[ID], -2, MONTH)) + CALCULATE([NFC XR2 (Period Based)], DATEADD('REPORTING PERIOD'[ID], -3, MONTH)) + CALCULATE([NFC XR2 (Period Based)], DATEADD('REPORTING PERIOD'[ID], -4, MONTH)) + CALCULATE([NFC XR2 (Period Based)], DATEADD('REPORTING PERIOD'[ID], -5, MONTH)) + CALCULATE([NFC XR2 (Period Based)], DATEADD('REPORTING PERIOD'[ID], -6, MONTH)) + CALCULATE([NFC XR2 (Period Based)], DATEADD('REPORTING PERIOD'[ID], -7, MONTH)) + CALCULATE([NFC XR2 (Period Based)], DATEADD('REPORTING PERIOD'[ID], -8, MONTH)) + CALCULATE([NFC XR2 (Period Based)], DATEADD('REPORTING PERIOD'[ID], -9, MONTH)) + CALCULATE([NFC XR2 (Period Based)], DATEADD('REPORTING PERIOD'[ID], -10, MONTH)) + CALCULATE([NFC XR2 (Period Based)], DATEADD('REPORTING PERIOD'[ID], -11, MONTH)) ) // NFC R12 X-rate (Period Based): The sum of [NFC XR2 (Period Based)] for the current month and [NFC XR2 (Period Based)] values from each of the previous 11 months. // NOTE: This measure can only to be presented on Reporting Period level. Can not be used on Quarter, Half year or Year level. | |
| NFC R12 XR2 (YTD Based) | Y | Y | SUM | N | SUMX ( VALUES ( COMPANY[ID] ), [NFC R12]*[Conversion rate XR2]) // NFC R12 X-rate (YTD Based): The R12-value multiplied with the rate at the that specific Reporting period. // VALUES-statement to handle the multi-company scenario where companies have different conversion rates. | |
| NFC Trans | Y | N | SUM | N | SUM('GL ANALYSIS'[NFC_PERIOD_CURR]) | |
| NFC XR1 (Period Based) | Y | N | SUM | N | IF(HASONEVALUE('CURRENCY CODE X-RATE 1'[Currency Code X-Rate 1]), VAR RestatedAmount = ADDCOLUMNS( SUMMARIZE('GL ANALYSIS' ,'REPORTING PERIOD'[ID],'REPORTING FROM CURRENCY'[Currency Code]) ,"@GLAmount",CALCULATE([NFC]) ,"@XRates" ,CALCULATE(SELECTEDVALUE('X-RATES 1'[CURRENCYRATE])) ) VAR XAmount = SUMX(RestatedAmount, [@GLAmount] * [@XRates]) RETURN XAmount ) | |
| NFC XR1 (YTD Based) | Y | Y | SUM | N | VAR CurrPeriod = MAX('REPORTING PERIOD'[Reporting Period]) RETURN IF('GL ANALYSIS MEASURES'[HasOneValuePeriod] = FALSE(), BLANK(), IF( CurrPeriod = 1, SUMX (VALUES ( COMPANY[ID] ), ([NFC]*[Conversion rate XR1]) ), SUMX (VALUES ( COMPANY[ID] ), ([NFC YTD]*[Conversion rate XR1]) - ([NFC OB]*[Conversion rate PP XR1])) ) ) // NFC X-rate (YTD Based): (NFC YTD X-rate) minus (OB * rate in previous period). Note that OB is the same as the YTD-value in the previous period. // In case no addition of NFC from previous period, this is just the effect of the same YTD value being multiplied with rate in previous period compared with the rate in this period. // NOTE: This measure can only to be presented on Reporting Period level. Can not be used on Quarter, Half year or Year level. HasOnValuePeriod is to ensure Blank values. | |
| NFC XR2 (Period Based) | Y | N | SUM | N | IF(HASONEVALUE('CURRENCY CODE X-RATE 2'[Currency Code X-Rate 2]), VAR RestatedAmount = ADDCOLUMNS( SUMMARIZE('GL ANALYSIS' ,'REPORTING PERIOD'[ID],'REPORTING FROM CURRENCY 2'[Currency Code]) ,"@GLAmount",CALCULATE([NFC]) ,"@XRates" ,CALCULATE(SELECTEDVALUE('X-RATES 2'[CURRENCYRATE])) ) VAR XAmount = SUMX(RestatedAmount, [@GLAmount] * [@XRates]) RETURN XAmount ) | |
| NFC XR2 (YTD Based) | Y | Y | SUM | N | VAR CurrPeriod = MAX('REPORTING PERIOD'[Reporting Period]) RETURN IF('GL ANALYSIS MEASURES'[HasOneValuePeriod] = FALSE(), BLANK(), IF( CurrPeriod = 1, SUMX (VALUES ( COMPANY[ID] ), ([NFC]*[Conversion rate XR2]) ), SUMX (VALUES ( COMPANY[ID] ), ([NFC YTD]*[Conversion rate XR2]) - ([NFC OB]*[Conversion rate PP XR2])) ) ) // NFC X-rate (YTD Based): (NFC YTD X-rate) minus (OB * rate in previous period). Note that OB is the same as the YTD-value in the previous period. // In case no addition of NFC from previous period, this is just the effect of the same YTD value being multiplied with rate in previous period compared with the rate in this period. // NOTE: This measure can only to be presented on Reporting Period level. Can not be used on Quarter, Half year or Year level. HasOnValuePeriod is to ensure Blank values. | |
| NFC YTD Trans | Y | Y | SUM | N | CALCULATE([NFC Trans], DATESYTD('REPORTING PERIOD'[ID])) + CALCULATE(SUM('GL ANALYSIS'[NFC_OB_PERIOD_CURR]), DATESYTD('REPORTING PERIOD'[ID])) | |
| NFC YTD XR1 (Period Based) | Y | Y | SUM | N | CALCULATE ( [NFCOBPeriodZeroXR1], DATESYTD ( 'REPORTING PERIOD'[ID] ) ) + CALCULATE ( [NFC XR1 (Period Based)], DATESYTD ( 'REPORTING PERIOD'[ID] ) ) // NFC YTD X-rate (Period Based): This YTD value includes the sum of both OB and NFC values. | |
| NFC YTD XR1 (YTD Based) | Y | Y | SUM | N | SUMX( VALUES( COMPANY[ID] ), CALCULATE( [NFC YTD] * [Conversion rate XR1] )) // NFC YTD X-rate (YTD Based): The YTD value multiplied with the current rate. // VALUES-statement to handle the multi-company scenario where companies have different conversion rates. // If ever to update this code: Heads up on verifing the results on Quarter and Year level in the multi-company scenario. | |
| NFC YTD XR2 (Period Based) | Y | Y | SUM | N | CALCULATE ( [NFCOBPeriodZeroXR2], DATESYTD ( 'REPORTING PERIOD'[ID] ) ) + CALCULATE ( [NFC XR2 (Period Based)], DATESYTD ( 'REPORTING PERIOD'[ID] ) ) // NFC YTD X-rate (Period Based): This YTD value includes the sum of both OB and NFC values. | |
| NFC YTD XR2 (YTD Based) | Y | Y | SUM | N | SUMX( VALUES( COMPANY[ID] ), CALCULATE( [NFC YTD] * [Conversion rate XR2] )) // NFC YTD X-rate (YTD Based): The YTD value multiplied with the current rate. // VALUES-statement to handle the multi-company scenario where companies have different conversion rates. // If ever to update this code: Heads up on verifing the results on Quarter and Year level in the multi-company scenario. | |
| NFCOBPeriodZero | Y | Y | SUM | N | //Calulcates the incoming balance related to [NFC], acc period 0, as defined in the underlying data source // The idea is to calculate the same value for all periods VAR UntilDate = MAX('REPORTING PERIOD'[Period Until Date]) VAR LastDatePY = MAX('REPORTING PERIOD'[Max Reporting Date PY]) VAR DiffInDays = DATEDIFF(LastDatePY, UntilDate, DAY) RETURN CALCULATE ( // go back to last data in previous reporting year + 1 [NFC OB], DATEADD('REPORTING PERIOD'[ID],-DiffInDays+1, DAY) ) | |
| NFCOBPeriodZeroXR1 | Y | Y | SUM | N | IF ( HASONEVALUE ( 'CURRENCY CODE X-RATE 1'[Currency Code X-Rate 1] ), VAR RestatedAmount = ADDCOLUMNS ( SUMMARIZE ( 'GL ANALYSIS','REPORTING PERIOD'[ID],'REPORTING FROM CURRENCY'[Currency Code] ), "@GLAmount", CALCULATE ( SUM ( 'GL ANALYSIS'[NFC_OB_PERIOD_DOM] ) ), "@XRATE", CALCULATE ( SELECTEDVALUE ( 'X-RATES 1'[PREV_YEAR_LAST_PERIOD_RATE]) ) ) VAR XAmount = SUMX ( RestatedAmount, [@GLAmount] * [@XRATE] ) RETURN XAmount ) // NFCOBPeriodZeroXR1: Calculates the OB X-rate value buy sum all OB-values and multiply with the last rate from previous year. // NOTE: If OB values exists on other periods than the start of the year, then they also is multiplied with the last rate from previous year (not previous period). | |
| NFCOBPeriodZeroXR2 | Y | Y | SUM | N | IF ( HASONEVALUE ( 'CURRENCY CODE X-RATE 2'[Currency Code X-Rate 2] ), VAR RestatedAmount = ADDCOLUMNS ( SUMMARIZE ( 'GL ANALYSIS','REPORTING PERIOD'[ID],'REPORTING FROM CURRENCY'[Currency Code] ), "@GLAmount", CALCULATE ( SUM ( 'GL ANALYSIS'[NFC_OB_PERIOD_DOM] ) ), "@XRATE", CALCULATE ( SELECTEDVALUE ( 'X-RATES 2'[PREV_YEAR_LAST_PERIOD_RATE]) ) ) VAR XAmount = SUMX ( RestatedAmount, [@GLAmount] * [@XRATE] ) RETURN XAmount ) // NFCOBPeriodZeroXR2: Calculates the OB X-rate value buy sum all OB-values and multiply with the last rate from previous year. // NOTE: If OB values exists on other periods than the start of the year, then they also is multiplied with the last rate from previous year (not previous period). | |
| Selected ActPY Measure | Y | CALCULATE(MIN('MEASURE LIST COMPARISON'[Row Index]), ALLEXCEPT('MEASURE LIST COMPARISON','MEASURE LIST COMPARISON'[Dim Comparison Id]), 'MEASURE LIST COMPARISON'[Dim Comparison Id]=1, 'MEASURE LIST COMPARISON'[Dim Currency Type Id]=VALUES('SWITCH CURRENCY TYPE'[Row Index]), 'MEASURE LIST COMPARISON'[Dim Time Perspective Id]=VALUES('SWITCH TIME PERSPECTIVE'[Row Index])) | ||||
| Selected Actual Measure | Y | MIN('MEASURE LIST ACTUAL'[Row Index]) | ||||
| Selected Actual XR1 (Period Based) Measure | Y | CALCULATE(MIN('MEASURE LIST ACTUAL'[Row Index]), ALLEXCEPT('MEASURE LIST ACTUAL','MEASURE LIST ACTUAL'[Dim Currency Type Id]), 'MEASURE LIST ACTUAL'[Dim Currency Type Id]=3, 'MEASURE LIST ACTUAL'[Dim Time Perspective Id]=VALUES('SWITCH TIME PERSPECTIVE'[Row Index])) | ||||
| Selected Actual XR2 (Period Based) Measure | Y | CALCULATE(MIN('MEASURE LIST ACTUAL'[Row Index]), ALLEXCEPT('MEASURE LIST ACTUAL','MEASURE LIST ACTUAL'[Dim Currency Type Id]), 'MEASURE LIST ACTUAL'[Dim Currency Type Id]=4, 'MEASURE LIST ACTUAL'[Dim Time Perspective Id]=VALUES('SWITCH TIME PERSPECTIVE'[Row Index])) | ||||
| Selected Bud Measure | Y | CALCULATE(MIN('MEASURE LIST COMPARISON'[Row Index]), ALLEXCEPT('MEASURE LIST COMPARISON','MEASURE LIST COMPARISON'[Dim Comparison Id]), 'MEASURE LIST COMPARISON'[Dim Comparison Id]=2, 'MEASURE LIST COMPARISON'[Dim Currency Type Id]=VALUES('SWITCH CURRENCY TYPE'[Row Index]), 'MEASURE LIST COMPARISON'[Dim Time Perspective Id]=VALUES('SWITCH TIME PERSPECTIVE'[Row Index])) | ||||
| Selected CFC Measure | Y | CALCULATE(MIN('MEASURE LIST COMPARISON'[Row Index]), ALLEXCEPT('MEASURE LIST COMPARISON','MEASURE LIST COMPARISON'[Dim Comparison Id]), 'MEASURE LIST COMPARISON'[Dim Comparison Id]=3, 'MEASURE LIST COMPARISON'[Dim Currency Type Id]=VALUES('SWITCH CURRENCY TYPE'[Row Index]), 'MEASURE LIST COMPARISON'[Dim Time Perspective Id]=VALUES('SWITCH TIME PERSPECTIVE'[Row Index])) | ||||
| Selected Comparison Measure | Y | MIN('MEASURE LIST COMPARISON'[Row Index]) | ||||
| Selected Comparison Switch | Y | MIN('SWITCH COMPARISON MEASURE'[Row Index]) | ||||
| Selected Currency Switch | Y | MIN('SWITCH CURRENCY TYPE'[Row Index]) | ||||
| Selected DisplayUnit Switch | Y | MIN('SWITCH DISPLAY UNIT'[Factor]) | ||||
| Selected NFC Measure | Y | CALCULATE(MIN('MEASURE LIST COMPARISON'[Row Index]), ALLEXCEPT('MEASURE LIST COMPARISON','MEASURE LIST COMPARISON'[Dim Comparison Id]), 'MEASURE LIST COMPARISON'[Dim Comparison Id]=4, 'MEASURE LIST COMPARISON'[Dim Currency Type Id]=VALUES('SWITCH CURRENCY TYPE'[Row Index]), 'MEASURE LIST COMPARISON'[Dim Time Perspective Id]=VALUES('SWITCH TIME PERSPECTIVE'[Row Index])) | ||||
| Selected Time Switch | Y | MIN('SWITCH TIME PERSPECTIVE'[Row Index]) | ||||
| Switch Actual | N | [Selected DisplayUnit Switch] * SWITCH([Selected Actual Measure], 1, [ACT], 2, [ACT YTD], 3, [ACT R12], 4, [ACT Trans], 5, [ACT YTD Trans], 6, [ACT R12 Trans], 7, [ACT XR1 (Period Based)], 8, [ACT YTD XR1 (Period Based)], 9, [ACT R12 XR1 (Period Based)], 10, [ACT XR2 (Period Based)], 11, [ACT YTD XR2 (Period Based)], 12, [ACT R12 XR2 (Period Based)], 13, [ACT XR1 (YTD Based)], 14, [ACT YTD XR1 (YTD Based)], 15, [ACT R12 XR1 (YTD Based)], 16, [ACT XR2 (YTD Based)], 17, [ACT YTD XR2 (YTD Based)], 18, [ACT R12 XR2 (YTD Based)] ) | ||||
| Switch Actual (Acc Calendar) | N | [Selected DisplayUnit Switch] * SWITCH([Selected Actual Measure], 1, [ACT (Acc Calendar)], 2, [ACT YTD (Acc Calendar)] ) | ||||
| Switch Actual (Balance Sheet Drill Through) | N | [Switch Actual] | ||||
| Switch Actual (For Drill Through) | N | [Switch Actual] | ||||
| Switch Comp (ActPY) | N | [Selected DisplayUnit Switch] * SWITCH([Selected ActPY Measure], 1, [ACT PY], 5, [ACT PY Trans], 9, [ACT PY XR1 (Period Based)], 13, [ACT PY XR2 (Period Based)], 17, [ACT PY XR1 (YTD Based)], 21, [ACT PY XR2 (YTD Based)], 25, [ACT PY YTD], 29, [ACT PY YTD Trans], 33, [ACT PY YTD XR1 (Period Based)], 37, [ACT PY YTD XR2 (Period Based)], 41, [ACT PY YTD XR1 (YTD Based)], 45, [ACT PY YTD XR2 (YTD Based)], 49, [ACT PY R12], 53, [ACT PY R12 Trans], 57, [ACT PY R12 XR1 (Period Based)], 61, [ACT PY R12 XR2 (Period Based)], 65, [ACT PY R12 XR1 (YTD Based)], 69, [ACT PY R12 XR2 (YTD Based)] ) | ||||
| Switch Comp (Bud) | N | [Selected DisplayUnit Switch] * SWITCH([Selected Bud Measure], 2, [BUD], 6, [BUD Trans], 10, [BUD XR1 (Period Based)], 14, [BUD XR2 (Period Based)], 18, [BUD XR1 (YTD Based)], 22, [BUD XR2 (YTD Based)], 26, [BUD YTD], 30, [BUD YTD Trans], 34, [BUD YTD XR1 (Period Based)], 38, [BUD YTD XR2 (Period Based)], 42, [BUD YTD XR1 (YTD Based)], 46, [BUD YTD XR2 (YTD Based)], 50, [BUD R12], 54, [BUD R12 Trans], 58, [BUD R12 XR1 (Period Based)], 62, [BUD R12 XR2 (Period Based)], 66, [BUD R12 XR1 (YTD Based)], 70, [BUD R12 XR2 (YTD Based)] ) | ||||
| Switch Comp (CFC) | N | [Selected DisplayUnit Switch] * SWITCH([Selected CFC Measure], 3, [CFC], 7, [CFC Trans], 11, [CFC XR1 (Period Based)], 15, [CFC XR2 (Period Based)], 19, [CFC XR1 (YTD Based)], 23, [CFC XR2 (YTD Based)], 27, [CFC YTD], 31, [CFC YTD Trans], 35, [CFC YTD XR1 (Period Based)], 39, [CFC YTD XR2 (Period Based)], 43, [CFC YTD XR1 (YTD Based)], 47, [CFC YTD XR2 (YTD Based)], 51, [CFC R12], 55, [CFC R12 Trans], 59, [CFC R12 XR1 (Period Based)], 63, [CFC R12 XR2 (Period Based)], 67, [CFC R12 XR1 (YTD Based)], 71, [CFC R12 XR2 (YTD Based)] ) | ||||
| Switch Comp (NFC) | N | [Selected DisplayUnit Switch] * SWITCH([Selected NFC Measure], 4, [NFC], 8, [NFC Trans], 12, [NFC XR1 (Period Based)], 16, [NFC XR2 (Period Based)], 20, [NFC XR1 (YTD Based)], 24, [NFC XR2 (YTD Based)], 28, [NFC YTD], 32, [NFC YTD Trans], 36, [NFC YTD XR1 (Period Based)], 40, [NFC YTD XR2 (Period Based)], 44, [NFC YTD XR1 (YTD Based)], 48, [NFC YTD XR2 (YTD Based)], 52, [NFC R12], 56, [NFC R12 Trans], 60, [NFC R12 XR1 (Period Based)], 64, [NFC R12 XR2 (Period Based)], 68, [NFC R12 XR1 (YTD Based)], 72, [NFC R12 XR2 (YTD Based)] ) | ||||
| Switch Comparison | N | [Selected DisplayUnit Switch] * SWITCH([Selected Comparison Measure], 1, [ACT PY], 2, [BUD], 3, [CFC], 4, [NFC], 5, [ACT PY Trans], 6, [BUD Trans], 7, [CFC Trans], 8, [NFC Trans], 9, [ACT PY XR1 (Period Based)], 10, [BUD XR1 (Period Based)], 11, [CFC XR1 (Period Based)], 12, [NFC XR1 (Period Based)], 13, [ACT PY XR2 (Period Based)], 14, [BUD XR2 (Period Based)], 15, [CFC XR2 (Period Based)], 16, [NFC XR2 (Period Based)], 17, [ACT PY XR1 (YTD Based)], 18, [BUD XR1 (YTD Based)], 19, [CFC XR1 (YTD Based)], 20, [NFC XR1 (YTD Based)], 21, [ACT PY XR2 (YTD Based)], 22, [BUD XR2 (YTD Based)], 23, [CFC XR2 (YTD Based)], 24, [NFC XR2 (YTD Based)], 25, [ACT PY YTD], 26, [BUD YTD], 27, [CFC YTD], 28, [NFC YTD], 29, [ACT PY YTD Trans], 30, [BUD YTD Trans], 31, [CFC YTD Trans], 32, [NFC YTD Trans], 33, [ACT PY YTD XR1 (Period Based)], 34, [BUD YTD XR1 (Period Based)], 35, [CFC YTD XR1 (Period Based)], 36, [NFC YTD XR1 (Period Based)], 37, [ACT PY YTD XR2 (Period Based)], 38, [BUD YTD XR2 (Period Based)], 39, [CFC YTD XR2 (Period Based)], 40, [NFC YTD XR2 (Period Based)], 41, [ACT PY YTD XR1 (YTD Based)], 42, [BUD YTD XR1 (YTD Based)], 43, [CFC YTD XR1 (YTD Based)], 44, [NFC YTD XR1 (YTD Based)], 45, [ACT PY YTD XR2 (YTD Based)], 46, [BUD YTD XR2 (YTD Based)], 47, [CFC YTD XR2 (YTD Based)], 48, [NFC YTD XR2 (YTD Based)], 49, [ACT PY R12], 50, [BUD R12], 51, [CFC R12], 52, [NFC R12], 53, [ACT PY R12 Trans], 54, [BUD R12 Trans], 55, [CFC R12 Trans], 56, [NFC R12 Trans], 57, [ACT PY R12 XR1 (Period Based)], 58, [BUD R12 XR1 (Period Based)], 59, [CFC R12 XR1 (Period Based)], 60, [NFC R12 XR1 (Period Based)], 61, [ACT PY R12 XR2 (Period Based)], 62, [BUD R12 XR2 (Period Based)], 63, [CFC R12 XR2 (Period Based)], 64, [NFC R12 XR2 (Period Based)], 65, [ACT PY R12 XR1 (YTD Based)], 66, [BUD R12 XR1 (YTD Based)], 67, [CFC R12 XR1 (YTD Based)], 68, [NFC R12 XR1 (YTD Based)], 69, [ACT PY R12 XR2 (YTD Based)], 70, [BUD R12 XR2 (YTD Based)], 71, [CFC R12 XR2 (YTD Based)], 72, [NFC R12 XR2 (YTD Based)] ) | ||||
| Switch Diff | N | [Switch Actual] - [Switch Comparison] | ||||
| Switch Diff (%) | N | DIVIDE([Switch Diff],ABS([Switch Comparison]), Blank()) | ||||
| Switch Diff (ActPY%) | N | DIVIDE([Switch Diff (ActPY)],ABS([Switch Comp (ActPY)]), Blank()) | ||||
| Switch Diff (ActPY) | N | [Switch Actual] - [Switch Comp (ActPY)] | ||||
| Switch Diff (Bud%) | N | DIVIDE([Switch Diff (Bud)],ABS([Switch Comp (Bud)]), Blank()) | ||||
| Switch Diff (Bud) | N | [Switch Actual] - [Switch Comp (Bud)] | ||||
| Switch XR Diff | N | [Switch XR1 (Period Based)] - [Switch XR2 (Period Based)] | ||||
| Switch XR Diff (%) | N | DIVIDE([Switch XR Diff],ABS([Switch XR2 (Period Based)]), Blank()) | ||||
| Switch XR1 (Period Based) | N | [Selected DisplayUnit Switch] * SWITCH([Selected Actual XR1 (Period Based) Measure], 7, [ACT XR1 (Period Based)], 8, [ACT YTD XR1 (Period Based)], 9, [ACT R12 XR1 (Period Based)] ) | ||||
| Switch XR2 (Period Based) | N | [Selected DisplayUnit Switch] * SWITCH([Selected Actual XR2 (Period Based) Measure], 10, [ACT XR2 (Period Based)], 11, [ACT YTD XR2 (Period Based)], 12, [ACT R12 XR2 (Period Based)] ) |
Time based measures are calculated in the cube with respect to other measures. The table below defines the meaning/definition of the different time measures. These measures need to be used with a specific time dimension hierarchy [REPORTING_DATE].[RepDateMFHy].
| Measure | Meaning |
| YTD |
Aggregated value for current month in current year. E.g.
for period 202010 Ytd represents sum of periods 202000-202010. |
| R12 |
For a given period, the sum all months from one year back up to the
previous month. E.g. Rolling 12 for 202010 is the sum of months 201910
- 202009 |
| PY |
Previous Year The value of the corresponding month one year back. E.g. for month 202010, the value of month 201910. |
| YTD PY |
Year to Date Previous Year Aggregated value for corresponding month one year back. E.g. for month 202010 YTD Previous Year represents sum of months 201901-201910. |
| R12 PY |
Rolling 12 (completed) Months Previous Year For a given month, first going one year back, then taking the sum of all months from one (more) year back up to the previous month. E.g. for month 202010 the sum of month 201810-201909 |
| Avg XXX Ytd | Sum XXX YTD/Count XXX YTD |
| Avg XXX R12 | Sum XXX R12/Count XXX R12 |
| XXX % YTD | Count XXX YTD/Count YYY YTD |
| XXX % R12 | Count XXX R12/Count XXX R12 |
| Model Name | Product Area |
| General Ledger | Financials |
The data source for this object is defined using IFS Developer Tool. The data source contains a number of transformations where the model reads data from the last step, i.e. the Data source view name (DW Source View Name).
Additional information can be found in the online documentation of Information Sources.
| Model Table | DW Source view (SQL Server) | BI Access View | Referenced Information Source | Recommended Access Type |
| GL ANALYSIS MEASURES | FACT_TRANS_BAL_SET_AM_CONF_TM | FACT_GL_TRANSACTION_BI, FACT_GL_BALANCE_BI, FACT_GL_PERIOD_BUDGET_BI, FACT_PLAN_TRANS_VALID_BI | FACT_GL_TRANSACTION, FACT_GL_BALANCE, FACT_GL_PERIOD_BUDGET, FACT_PLAN_TRANS_VALID | Data Mart |